package com.wstuo.itsm.knowledge.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Disjunction;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.wstuo.common.config.customfilter.dto.EasyExpression;
import com.wstuo.common.config.customfilter.entity.CustomExpression;
import com.wstuo.common.config.customfilter.entity.CustomFilter;
import com.wstuo.itsm.knowledge.dto.KnowledgeQueryDTO;
import com.wstuo.itsm.knowledge.entity.KnowledgeInfo;
import com.wstuo.common.dao.BaseDAOImplHibernate;
import com.wstuo.common.dto.PageDTO;
import com.wstuo.common.util.DaoUtils;
import com.wstuo.common.util.StringUtils;
import com.wstuo.common.util.TimeUtils;

/**
 * 知识库DAO类.
 * 
 * @author QXY
 */
public class KnowledgeDAO extends BaseDAOImplHibernate<KnowledgeInfo> implements
		IKnowledgeDAO {

	/**
	 * 分页查找知识信息.
	 * 
	 * @param querydto
	 * @param start
	 * @param limit
	 * @return PageDTO
	 */
	public PageDTO findAllKnowledgeInfos(KnowledgeQueryDTO querydto, int start,
			int limit, String sidx, String sord) {
		DetachedCriteria dc = DetachedCriteria.forClass(KnowledgeInfo.class);
		//创建条件别名
		dc = createConditionAlias(querydto, dc);
		//查询条件
		dc = searchCondition(querydto, dc);
		// 排序
		dc = DaoUtils.orderBy(sidx, sord, dc);
		// 最新知识，最热知识
		if (querydto.getQueryType() != null) {
			if (querydto.getQueryType().equals("new")) {
				dc.addOrder(Order.desc("addTime"));
			}
			if (querydto.getQueryType().equals("pop")) {
				dc.addOrder(Order.desc("clickRate"));
			}
		}
		return super.findPageByCriteria(dc, start, limit, "abc");
	}
	/**
	 * 查询条件
	 * @param querydto
	 * @param dc
	 * @return
	 */
	private DetachedCriteria searchCondition(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		if (querydto != null) {

			// 根据类型搜索知识库
			dc = searchKnowledgeOpt(querydto, dc);
			// 搜索基础数据
			dc = searchBaseAttribute(querydto, dc);
			// 搜索关联项
			dc = searchRelation(querydto, dc);
			// 根据时间搜索
			dc = searchByTime(querydto, dc);
		} else {
			dc.add(Restrictions.eq("knowledgeStatus", "1"));
		}
		return dc;
	}

	/**
	 * 创建条件别名
	 * @param querydto
	 * @param dc
	 */
	private DetachedCriteria createConditionAlias(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		if (querydto != null) {
			if (StringUtils.hasText(querydto.getCreatorFullName())) {
				dc.createAlias("create", "crt");
			}
			if (querydto.getServiceDirectoryItemNo() != null) {// 关联服务
				dc.createAlias("serviceDirectoryItem", "sd");
			}
			dc.createAlias("category", "c");
			if (querydto.getKnowledgeServiceNo() != null
					&& querydto.getKnowledgeServiceNo().length > 0) {
				dc.createAlias("serviceLists", "sl");
			}
		}
		return dc;
	}

	/**
	 * 搜索基础数据
	 * 
	 * @param querydto
	 * @param dc
	 * @param flag
	 */
	private DetachedCriteria searchBaseAttribute(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		if (querydto.getKids() != null && querydto.getKids().length > 0) {
			dc.add(Restrictions.in("kid", querydto.getKids()));
		}
		if (StringUtils.hasText(querydto.getCreator())) {
			dc.add(Restrictions.like("creator", querydto.getCreator(),
					MatchMode.ANYWHERE));
		}
		if (querydto.getCategoryNames() != null
				&& querydto.getCategoryNames().length > 0) {
			dc.add(Restrictions.in("c.eventName", querydto.getCategoryNames()));
		}
		if (querydto.getEventIds() != null && querydto.getEventIds().length > 0) {
			dc.add(Restrictions.or(
					Restrictions.in("c.eventId", querydto.getEventIds()),
					Restrictions.isNull("c.eventId")));
		}else {
			dc.add(Restrictions.isNull("c.eventId"));
		}
		if (querydto.getEnableCategorys() != null&& querydto.getEnableCategorys().length > 0) {// 二次过滤
			dc.add(Restrictions.in("c.eventId", querydto.getEnableCategorys()));
		}
		if (StringUtils.hasText(querydto.getKeyWord())) {
			dc.add(Restrictions.like("keyWords", querydto.getKeyWord(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(querydto.getTitle())) {
			dc.add(Restrictions.like("title", querydto.getTitle(),
					MatchMode.ANYWHERE));
		}
		if (StringUtils.hasText(querydto.getContent())) {
			dc.add(Restrictions.like("content", querydto.getContent(),
					MatchMode.ANYWHERE));
		}
		return dc;
	}

	/**
	 * 根据时间搜索
	 * 
	 * @param querydto
	 * @param dc
	 */
	private DetachedCriteria searchByTime(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		// 时间搜索
		if (querydto.getStartTime() != null && querydto.getEndTime() == null) {
			dc.add(Restrictions.ge("addTime", querydto.getStartTime()));
		}
		if (querydto.getStartTime() == null && querydto.getEndTime() != null) {
			Calendar endTimeCl = new GregorianCalendar();
			endTimeCl.setTime(querydto.getEndTime());
			endTimeCl.set(Calendar.DATE, endTimeCl.get(Calendar.DATE) + 1);
			dc.add(Restrictions.le("addTime", endTimeCl.getTime()));
		}
		if (querydto.getStartTime() != null && querydto.getEndTime() != null) {
			Calendar endTimeCl = new GregorianCalendar();
			endTimeCl.setTime(querydto.getEndTime());
			endTimeCl.set(Calendar.DATE, endTimeCl.get(Calendar.DATE) + 1);
			dc.add(Restrictions.and(
					Restrictions.le("addTime", endTimeCl.getTime()),
					Restrictions.ge("addTime", querydto.getStartTime())));
		}
		if (StringUtils.hasText(querydto.getAttachmentContent())) {
			dc.createAlias("attachements", "attr");
			dc.add(Restrictions.or(Restrictions.like("attr.attachmentContent",
					querydto.getAttachmentContent(), MatchMode.ANYWHERE),
					Restrictions.like("attr.attachmentName",
							querydto.getAttachmentContent(), MatchMode.ANYWHERE)));
		}
		return dc;
	}

	/**
	 * 搜索关联项
	 * 
	 * @param querydto
	 * @param dc
	 */
	private DetachedCriteria searchRelation(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		if (StringUtils.hasText(querydto.getCreatorFullName())) {
			dc.add(Restrictions.like("crt.fullName",
					querydto.getCreatorFullName(),
					MatchMode.ANYWHERE));
		}
		if (querydto.getServiceDirectoryItemNo() != null) {// 关联服务
			dc.add(Restrictions.eq("sd.subServiceId",
					querydto.getServiceDirectoryItemNo()));
		}
		if (querydto.getKnowledgeServiceNo() != null
				&& querydto.getKnowledgeServiceNo().length > 0) {
			dc.add(Restrictions.in("sl.eventId",
					querydto.getKnowledgeServiceNo()));
		}
		return dc;
	}

	/**
	 * 根据类型搜索知识库
	 * 
	 * @param querydto
	 * @param dc
	 * @param flag
	 * @return
	 */
	private DetachedCriteria searchKnowledgeOpt(KnowledgeQueryDTO querydto,
			DetachedCriteria dc) {
		if (querydto.getOpt() != null && querydto.getOpt().equals("my")) {// 我发布的知识
			dc.add(Restrictions.eq("creator", querydto.getCreatorName()));
			dc.add(Restrictions.eq("knowledgeStatus", "1"));
		}
		if (querydto.getOpt() != null && querydto.getOpt().equals("myap")) {// 我的待审核知识
			dc.add(Restrictions.eq("creator", querydto.getCreatorName()));
			dc.add(Restrictions.eq("knowledgeStatus", "0"));
		}
		if (querydto.getOpt() != null && querydto.getOpt().equals("myapf")) {// 我未审核通过的知识
			dc.add(Restrictions.eq("creator", querydto.getCreatorName()));
			dc.add(Restrictions.eq("knowledgeStatus", "-1"));
		}
		if (querydto.getOpt() != null && querydto.getOpt().equals("allapf")) {// 所有未通过审核知识
			dc.add(Restrictions.eq("knowledgeStatus", "-1"));
		}
		if (querydto.getOpt() != null && querydto.getOpt().equals("allap")) {// 所有待审
			dc.add(Restrictions.eq("knowledgeStatus", "0"));
		}
		if (querydto.getOpt() != null
				&& !KnowledgeQueryDTO.isContainOpt(querydto.getOpt())) {
			dc.add(Restrictions.eq("knowledgeStatus", "1"));
		}
		return dc;
	}

	/**
	 * 查找热门知识/最新知识.
	 * 
	 * @param queryType
	 * @param categoryIds
	 * @return List<KnowledgeInfo>
	 */
	@SuppressWarnings("unchecked")
	public List<KnowledgeInfo> findKnowledgeByIds(String queryType,
			Long[] categoryIds) {
		final DetachedCriteria dc = DetachedCriteria
				.forClass(KnowledgeInfo.class);
		// 最新知识，最热知识
		if (queryType != null) {
			if (queryType.equals("new")) {
				dc.addOrder(Order.desc("addTime"));
			}
			if (queryType.equals("pop")) {
				dc.addOrder(Order.desc("clickRate"));
			}
			dc.add(Restrictions.eq("knowledgeStatus", "1"));
		}
		if (categoryIds != null && categoryIds.length > 0) {// 分类权限
			dc.createAlias("category", "c");
			dc.add(Restrictions.in("c.eventId", categoryIds));
		}
		return super.getHibernateTemplate().findByCriteria(dc, 0, 10);
	}
	
	/**
	 * 根据条件统计
	 * @param qdto
	 * @return
	 */
	public Integer countChangeByType(KnowledgeQueryDTO qdto){
		int result = 0;
		DetachedCriteria dc = DetachedCriteria.forClass(KnowledgeInfo.class);
		if (qdto != null) {
			// 创建条件别名
			dc = createConditionAlias(qdto, dc);
			// 查询条件
			dc = searchCondition(qdto, dc);
			result = super.statCriteria(dc);
		}
		return result;
	}
	@SuppressWarnings("unchecked")
	public List<KnowledgeInfo> findListKnowledgeInfos(KnowledgeQueryDTO querydto,
			String sidx, String sord) {
		DetachedCriteria dc = DetachedCriteria.forClass(KnowledgeInfo.class);
		if (querydto != null) {
			// 创建条件别名
			dc = createConditionAlias(querydto, dc);
			// 查询条件
			dc = searchCondition(querydto, dc);
			// 公共的排序方法
			dc = DaoUtils.orderBy(sidx, sord, dc);
		}
		return super.getHibernateTemplate().findByCriteria(dc);
	}
	/**
     * 通过过滤器查询实体数据
     * @param filter
     * @return PageDTO
     * 
     */
	public PageDTO findPageByCustomFilter(CustomFilter filter,KnowledgeQueryDTO queryDTO,
			List<Long> liid, int start , int limit,String sidx,String sord) {
		PageDTO dto = new PageDTO();
		DetachedCriteria dc=getDcByCustomFilter(filter, queryDTO.getCompanyNos(), liid, sidx, sord);
		if (queryDTO.getCategoryIds() != null) {
			dc.createAlias("category", "ct").add(Restrictions.in("ct.eventId", queryDTO.getCategoryIds()));
		}
		if(dc!=null){
			dto = super.findPageByCriteria(dc, start, limit,"ServiceName");
		}
		return dto;
	}
	public DetachedCriteria getDcByCustomFilter(CustomFilter filter,Long [] companyNos,List<Long> liid,String sidx,String sord){
    	DetachedCriteria dc = DetachedCriteria.forClass(KnowledgeInfo.class);  
    	Disjunction dj = Restrictions.disjunction();
		Conjunction cj = Restrictions.conjunction();
		List<CustomExpression> exps = filter.getExpressions();
		List<CustomExpression> cexps =new ArrayList<CustomExpression>();
		dc.add(cj);
		cj.add(dj);
		//只查找我负责的公司 问题
		if(companyNos!=null && companyNos.length>0){
			dc.add(Restrictions.in("companyNo", companyNos));
		}
		boolean result=false;
		String joinTypes="";
		boolean res=true;
		if(exps!=null&&exps.size()>0){
			for(CustomExpression exp:exps){
				if(exp.getPropName().equals("serviceLists.eventId")){
					res=false;
					if(exp.getOperator().equals("=")){
						result=true;
					}
				}else{
					cexps.add(exp);
				}
				if(res){
					joinTypes=exp.getJoinType();
				}
			}
		}
		if(liid!=null && liid.size()>0){
			Long[] ids = new Long[liid.size()];
			liid.toArray(ids);
			if(result){
				if(joinTypes.equals("or")){
					dj.add(Restrictions.in("kid",ids));
				}else{
					cj.add(Restrictions.in("kid",ids));
				}
			}else{
				if(joinTypes.equals("or")){
					dj.add(Restrictions.not(Restrictions.in("kid",ids)));
				}else{
					cj.add(Restrictions.not(Restrictions.in("kid",ids)));
				}
			}
		}else{
			if(result){
				if(joinTypes.equals("or")){
					dj.add(Restrictions.eq("kid",-1L));
				}else{
					cj.add(Restrictions.eq("kid",-1L));
				}
			}
		}
		//排序
		dc = DaoUtils.orderBy(sidx, sord, dc);
		if(cexps!=null&&cexps.size()>0){
			//上一个的连接类型
			String joinType = "";
			for (CustomExpression exp:  cexps) {
				//如果上一个连接类型是and
				if("and".equals(joinType)){
					dj = Restrictions.disjunction();
					cj.add(dj);
				}
				dj.add(createEasyExpression(exp));
				
				joinType = exp.getJoinType();
			}
		}
		return dc;
	}
    /**
     * 根据过滤器条件创建表达式
     */
	private EasyExpression createEasyExpression(CustomExpression exp) {
		String propName = exp.getPropName();
		Object propValue = fetchPropValue(exp.getPropValue(), exp.getPropType());
		EasyExpression easyExp =new EasyExpression(propName, propValue, " "+exp.getOperator()+" ");
		return easyExp;
	}
	/**
     * 转换变量类型
     */
	private Object fetchPropValue(String propValue, String propType){
		Object Object=null;
		if(propType.equals("Long")){
			Object=Long.valueOf(propValue);
		}else if(propType.equals("Int")){
			Object=Integer.valueOf(propValue);
		}else if(propType.equals("Double")){
			Object=Double.valueOf(propValue);
		}else if(propType.equals("String")){
			Object=propValue;
		}else if(propType.equals("Data")){
			Object = TimeUtils.parse(propValue,TimeUtils.DATETIME_PATTERN);
		}else if(propType.equals("Boolean")){
			Object=Boolean.valueOf(propValue);
		}
		return Object;
	}
	
	/**
     * 通过过滤器查询实体数据
     * @param filter
     * @return PageDTO
     * 
     */
	public List<Object> findByServiceDirectoryFilter(CustomFilter filter) {
		List<CustomExpression> exps = filter.getExpressions();
		boolean result=false;
		String propValues="";
		if(exps!=null&&exps.size()>0){
			for(CustomExpression exp:exps){
				if(exp.getPropName().equals("serviceLists.eventId")){
					if(propValues.equals("")){
						propValues=exp.getPropValue();
					}else{
						propValues=propValues+","+exp.getPropValue();
					}
					result=true;
				}
			}
		}
		if(result){
			return getServiceDirectoryFilter(propValues);
		}else{
			return null;
		}
		
    }
	/**
	 * 组合查询
	 * @param propValues
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private List<Object> getServiceDirectoryFilter(final String propValues) {
		return this.getHibernateTemplate().executeFind(new HibernateCallback<List<Object>>() {

			public List<Object> doInHibernate(Session session) throws HibernateException,
					SQLException {
				String[] ids=propValues.split(",");
				StringBuilder stringBuilder = new StringBuilder();
						stringBuilder.append("SELECT kwd.kid FROM KnowledgeInfo kwd ");
						stringBuilder.append("LEFT JOIN KnowledgeInfo_EventCategory ke ON kwd.kid=ke.KnowledgeInfo_kid ");
						stringBuilder.append("LEFT JOIN EventCategory ec ON ke.serviceLists_EventId=ec.EventId ");
						for(String id:ids){
							stringBuilder.append("LEFT JOIN KnowledgeInfo_EventCategory ke"+id+" ON ke.KnowledgeInfo_kid = ke"+id+".KnowledgeInfo_kid ");
						}
						stringBuilder.append("WHERE 1=1 ");
						for(String id:ids){
							stringBuilder.append(" AND ke"+id+".serviceLists_EventId="+id);
						}
						stringBuilder.append(" GROUP BY kwd.kid");
						
				Query query = session
						.createSQLQuery(stringBuilder.toString())
						.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				
				List<Object> lists = query.list();
				return lists;
			}
		});
		
	}
	@SuppressWarnings("unchecked")
	public List<KnowledgeInfo> findListByCustomFilter(CustomFilter filter,
			Long[] companyNos, List<Long> liid, String sidx, String sord) {
		DetachedCriteria dc=getDcByCustomFilter(filter, companyNos, liid, sidx, sord);
		if(dc!=null)
			return super.getHibernateTemplate().findByCriteria(dc);
		else
			return null;
	}
}